package db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


/**
 * SQLite数据库的帮助类
 *
 * 该类属于扩展类,主要承担数据库初始化和版本升级使用,其他核心全由核心父类完成
 *
 */
public class DataBaseHelper extends MySQLiteOpenHelper {//SDCardSQLiteOpenHelper
	private String system_project = "CREATE TABLE [system_project]([id] TEXT NOT NULL, [name] TEXT, [code] TEXT, [region] TEXT, [linkMan] TEXT," +
			" [linkPhone] TEXT,[address] TEXT,[description] TEXT, [status] TEXT,  [creatorId] TEXT,  [creatorName] TEXT, [createTime] TEXT," +
			" [regionId] TEXT,[regionName] TEXT)";
	private String system_food_notes = "CREATE TABLE [system_food_notes]([id] TEXT NOT NULL, [name] TEXT,  [creatorId] TEXT,  [creatorName] TEXT, [createTime] TEXT , [status] TEXT)";

	private String config_meal_time = "CREATE TABLE [config_meal_time]([id] TEXT NOT NULL, [name] TEXT, [code] TEXT, [startTime] TEXT, [endTime] TEXT," +
			" [description] TEXT, [sortNo] INTEGER,  [creatorId] TEXT,  [creatorName] TEXT, [createTime] TEXT)";

	private String config_business_type = "CREATE TABLE [config_business_type]([id] TEXT NOT NULL, [name] TEXT , [code] TEXT,  " +
			"   [description] TEXT, [sortNo] INTEGER,  [creatorId] TEXT,  [creatorName] TEXT, [createTime] TEXT)";

	private String menu_food_category = "CREATE TABLE [menu_food_category]([id] TEXT NOT NULL, [nameZh] TEXT , [nameEn] TEXT, [code] TEXT,  " +
			"   [description] TEXT, [sortNo] INTEGER,  [creatorId] TEXT,  [creatorName] TEXT, [createTime] TEXT)";

	private String menu_food_item = "CREATE TABLE [menu_food_item]([id] TEXT NOT NULL, [nameZh] TEXT , [nameEn] TEXT, [code] TEXT, [mnemonicCode] " +
			"INTEGER, [foodCategoryId] TEXT, [measurementId] TEXT, [picturePath] TEXT, [opxItemId] TEXT, [barCode] TEXT, [description] TEXT, [sortNo] INTEGER, " +
			"[status] TEXT, [creatorId] TEXT, [creatorName] TEXT, [createTime] TEXT, [firstChar] TEXT, [xmlOut] TEXT, [foodCount] TEXT, " +
			"[storeroomId] TEXT, [pinYin] TEXT)";

	private String menu_food_price = "CREATE TABLE [menu_food_price]([id] TEXT NOT NULL, [mealTimeId] TEXT, [foodId] TEXT, [businessTypeId] TEXT, [price] FLOAT, [startTime] TEXT" +
			", [endTime] TEXT, [level] INTEGER, [priceVersion] TEXT, [status] TEXT, [creatorId] TEXT, [creatorName] TEXT, [createTime] TEXT, [xmlOut] TEXT)";
	private String ara_store_log = "CREATE TABLE [ara_store_log]([id] TEXT NOT NULL PRIMARY KEY, [city] TEXT ,  [education] TEXT ,  [ip] TEXT, " +
			" [amount] TEXT , [orderNo] TEXT , [isSyn] TEXT NOT NULL, [storeUserId] TEXT , [storeUserName] TEXT , [createTime] TEXT , " +
			" [creatorId] TEXT ,  [creatorName] TEXT ,  [isSuccess] TEXT NOT NULL,  [zfbAmount] TEXT,  [userCount] LONG , [zfType] TEXT)";

	private String trade_account_consumption_flow = "CREATE TABLE [trade_account_consumption_flow]([id] TEXT NOT NULL PRIMARY KEY, [restaurant] TEXT , " +
			"[mealTime] TEXT, [posMachine] TEXT , [customer] TEXT, [accountId] TEXT, [card] TEXT ," +
			"[operateType] TEXT,[settlementType] TEXT,[flowNo] TEXT,[beforeAmount] DOUBLE,[actualAmount] DOUBLE,[afterAmount] DOUBLE ,"+
			"[discountAmount] DOUBLE,[beforeTimes] INTEGER,[times] INTEGER,[afterTimes] INTEGER,[reason] TEXT,[isPay] TEXT,[creatorId] TEXT,[creatorName] TEXT," +
			"[createTime] TEXT,[isXmlOut] TEXT,[orderStatus] TEXT)";
	private String trade_consumption_detail = "CREATE TABLE [trade_consumption_detail]([id] TEXT NOT NULL PRIMARY KEY, [mainId] TEXT , [foodId] TEXT, [businessTypeId] TEXT,"+
			"[price] DOUBLE,[quantity] DOUBLE,[actualAmount] DOUBLE,[handDiscount] DOUBLE,[reason] TEXT,[isXmlOut] TEXT,[refundStatus] TEXT)";
	private String trade_consumption_main = "CREATE TABLE [trade_consumption_main]([id] TEXT NOT NULL PRIMARY KEY,  [restaurant] TEXT , " +
			"[mealTime] TEXT, [posMachine] TEXT , [flowNo] TEXT,[consumptionTime] TEXT,[orderType] TEXT,[customer] TEXT,[quantity] DOUBLE,[shouldPaidAmount] DOUBLE,[actualAmount] DOUBLE ,"+
			"[discountPercent] DOUBLE,[discountAmount] DOUBLE,[reason] TEXT,[quotaPrice] DOUBLE,[times] INTEGER,[isSettlement] TEXT,[creatorId] TEXT,[creatorName] TEXT," +
			"[createTime] TEXT,[isXmlOut] TEXT , [customerAddress] TEXT , [customerPhone] TEXT , [customerName] TEXT,[tableNo] TEXT,[customerArchitecture] TEXT,[printStatus] TEXT,[customerFloor] TEXT,[orderStatus] TEXT)";
	private String user_information = "CREATE TABLE [user_information]([id] TEXT NOT NULL PRIMARY KEY, [phone] TEXT NOT NULL , [name] TEXT NOT NULL, [bedNumber] TEXT NOT NULL ,[lastUseTime] TEXT NOT NULL)";

	public static final DateFormat MilliSECOND_FORMAT = new SimpleDateFormat("yyyyMMdd");

	private StringBuilder updateDataType =new StringBuilder()
			.append(" ALTER TABLE \"ara_store_log\" RENAME TO \"_ara_store_log_old_"+ (MilliSECOND_FORMAT.format(new Date())) +"\";")
			.append(ara_store_log)
			.append(" INSERT INTO \"ara_store_log\"(\"id\" , \"city\" , \"education\" , \"ip\", \"amount\" , \"orderNo\" , \"isSyn\" , \"storeUserId\" , \"storeUserName\" , \"createTime\" , \"creatorId\" ,  \"creatorName\" ,  \"isSuccess\",  \"zfbAmount\",  \"userCount\" , \"zfType\")" +
					" SELECT \"id\" , \"city\" , \"education\" , \"ip\", \"amount\" , \"orderNo\" , \"isSyn\" , \"storeUserId\" , \"storeUserName\" , \"createTime\" , \"creatorId\" ,  \"creatorName\" ,  \"isSuccess\",  \"round(zfbAmount,2)\",  \"userCount\" , \"zfType\"  FROM \"_ara_store_log_old_"+ (MilliSECOND_FORMAT.format(new Date())) + "\" ;")
			.append("DROP TABLE _ara_store_log_old_"+ (MilliSECOND_FORMAT.format(new Date())) + "\";");

	public DataBaseHelper(Context context, String name, CursorFactory factory,
						  int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(system_project);
		db.execSQL(menu_food_item);
		db.execSQL(config_business_type);
		db.execSQL(menu_food_category);
		db.execSQL(config_meal_time);
		db.execSQL(menu_food_price);

		db.execSQL(ara_store_log);
		db.execSQL(trade_account_consumption_flow);
		db.execSQL(trade_consumption_detail);
		db.execSQL(trade_consumption_main);
		db.execSQL(user_information);
		db.execSQL(system_food_notes);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch (oldVersion) {
			case 1:
                db.execSQL(updateDataType.toString());

		}
//		if (CommonValue.isDebug) Log.i("DataBaseHelper", "[onUpgrade]oldVersion:"+ oldVersion +"newVersion:"+newVersion+"...end.");
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
	}
}
